/*
 *  Copyright 2019-2020 Zheng Jie
 *
 *  Licensed under the Apache License, Version 2.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *  http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 */
package me.zhengjie.modules.biz.repository;

import me.zhengjie.modules.biz.domain.HazardousChemicals;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author Admin
 * @website https://eladmin.vip
 * @date 2023-03-03
 **/
public interface HazardousChemicalsRepository extends JpaRepository<HazardousChemicals, Integer>, JpaSpecificationExecutor<HazardousChemicals> {

    @Query(value = "SELECT DISTINCT name FROM hj_hazardous_chemicals", nativeQuery = true)
    ArrayList<String> collectName();

    @Query(value = "SELECT DISTINCT name FROM hj_hazardous_chemicals WHERE type = ?1 and status = 0", nativeQuery = true)
    ArrayList<String> collectNameByType(Integer type);


    @Query(value = "SELECT COUNT(name) FROM hj_hazardous_chemicals where name = ?1", nativeQuery = true)
    int getCount(String name);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals where name = ?1 LIMIT 0,1;", nativeQuery = true)
    HazardousChemicals Detail(String name);

    @Query(value = "SELECT amount from hj_hazardous_chemicals where name = ?1 and status = 0", nativeQuery = true)
    ArrayList<Float> findAmount(String hazardousChemicalsName);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals WHERE name = ?1 AND amount = ?2 and status = 0 LIMIT 0,1", nativeQuery = true)
    HazardousChemicals getByNameAmount(String name, float amount);

    @Query(value = "SELECT COUNT(name) FROM hj_hazardous_chemicals where name = ?1 AND amount = ?2 and status = 0", nativeQuery = true)
    float getCountByAmount(String name, float amount);

    @Query(value = "SELECT count_type FROM hj_hazardous_chemicals WHERE name = ?1 and status = 0", nativeQuery = true)
    int findCountType(String name);

    @Query(value = "SELECT amount FROM hj_hazardous_chemicals WHERE name = ?1", nativeQuery = true)
    ArrayList<Float> findCountNum(String name);

    @Query(value = "SELECT DISTINCT amount FROM hj_hazardous_chemicals", nativeQuery = true)
    ArrayList<Float> collectAmount();

    @Query(value = "SELECT  name, amount, SUM(CASE WHEN count_type = 1 THEN 1 END) AS number, SUM(CASE WHEN count_type = 2 THEN amount ELSE NULL END) AS weight FROM hj_hazardous_chemicals GROUP BY `name`", nativeQuery = true)
    Page<Object> findAllByNameAmount(Specification<HazardousChemicals> specification, Pageable pageable);

    @Query(value = "SELECT name,amount FROM hj_hazardous_chemicals group by name,amount", nativeQuery = true)
    HashMap<String, Float> collectNameAmount();

    @Query(value = "SELECT * FROM hj_hazardous_chemicals WHERE name = ?1 AND amount = ?2 LIMIT 0,1", nativeQuery = true)
    HazardousChemicals findByNameAmount(String name, Float amount);

    @Query(value = "SELECT capacity FROM hj_hazardous_chemicals where name = ?1  LIMIT 0,1", nativeQuery = true)
    Float getCapacityByName(String name);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals WHERE cabinet_id = ?1", nativeQuery = true)
    List<HazardousChemicals> findHcList(Integer id);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals WHERE name = ?1 AND  number = ?2 AND batch_number = ?3", nativeQuery = true)
    List<HazardousChemicals> findAllByNameNumber(String name, String number, String batchNumber);

    HazardousChemicals findByNameAndCategoryAndStorageAndType(String name, String category, int storage, int type);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals WHERE id=?1 and name = ?2 limit 0,1", nativeQuery = true)
    HazardousChemicals findByIdAndName(int hcId, String name);

    @Query(value = "SELECT distinct t.name FROM hj_hazardous_chemicals t where t.type = ?1", nativeQuery = true)
    List<Map<String, Object>> findNamesByType(int type);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals WHERE type=1 GROUP BY name", nativeQuery = true)
    List<HazardousChemicals> findDistanceEquipment();

    @Query(value = "SELECT * FROM hj_hazardous_chemicals WHERE id = ?1", nativeQuery = true)
    HazardousChemicals findSoloEquipment(Integer id);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals WHERE type=2 GROUP BY name,batch_number", nativeQuery = true)
    List<HazardousChemicals> findDistanceStandardSubstance();

    @Query(value = "SELECT * FROM hj_hazardous_chemicals WHERE type=3 GROUP BY name,grade", nativeQuery = true)
    List<HazardousChemicals> findDistanceChemicalReagent();

    @Query(value = "SELECT * FROM hj_hazardous_chemicals WHERE  cabinet_id = ?1", nativeQuery = true)
    List<HazardousChemicals> refrigeratorList(Integer id);

    @Query(value = "SELECT * FROM(SELECT name, SUM(amount), number, weight FROM (SELECT * FROM (SELECT name, amount, SUM(CASE WHEN count_type = 1 THEN 1 END) AS number, SUM(CASE WHEN count_type = 2 THEN amount ELSE NULL END) AS weight FROM hj_hazardous_chemicals GROUP BY name , amount) a WHERE a.weight > 0) b GROUP BY name UNION SELECT * FROM (SELECT name, amount, SUM(CASE WHEN count_type = 1 THEN 1 END) AS number, SUM(CASE WHEN count_type = 2 THEN amount ELSE NULL END) AS weight FROM hj_hazardous_chemicals GROUP BY name , amount) a WHERE a.number > 0) d", nativeQuery = true)
    List<Map<String, Object>> aabb();

    List<HazardousChemicals> findByNameAndType(String name, int type);

    List<HazardousChemicals> findByNameAndExpirationDateAndType(String name, Timestamp expirationDate, int type);

    HazardousChemicals findByNameAndBatchNumberAndTypeAndCabinetId(String name, String batchNumber, int type, int cabinetId);

    List<HazardousChemicals> findByNameAndBatchNumberAndType(String name, String batchNumber, Integer type);

    @Query(value = "SELECT count(*) from hj_hazardous_chemicals where name = ?1 and residue != 0", nativeQuery = true)
    Integer countBottle(String name);

    @Query(value = "select h.id from hj_hazardous_chemicals h where h.id not in (select hcs.id from hj_hazardous_chemicals hcs,hj_storehouse_order so,hj_storehouse_order_detail sod\n" +
            "where so.status = 5 and so.id = sod.order_id and\n" +
            "      sod.hj_hazardous_chemical_id = hcs.id and hcs.name = '硫酸钠') and h.name = '硫酸钠'", nativeQuery = true)
    List<Integer> findNoDeliveryByName(String name);

    @Query(value = "SELECT * FROM hj_hazardous_chemicals where  type = ?1 order by id desc", nativeQuery = true)
    List<HazardousChemicals> getByType(Integer type);
}
